/*
 * Copyright © 2016, Forp Co., LTD
 *
 * All Rights Reserved.
 */
package org.jeecg.common.datasource.jdbc;

import io.swagger.annotations.ApiModelProperty;
import oracle.sql.TIMESTAMP;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.jeecg.common.datasource.annotation.DBColumn;
import org.jeecg.common.datasource.annotation.DBTable;
import org.jeecg.common.datasource.enums.DataSourceType;
import org.jeecg.common.datasource.jdbc.vo.ClassMapping;
import org.jeecg.common.datasource.jdbc.vo.DBMappingProperty;
import org.jeecg.common.datasource.jdbc.vo.PageInfo;
import org.jeecg.common.datasource.jdbc.vo.PageSort;
import org.jeecg.common.datasource.utils.DBFUtil;
import org.jeecg.common.exception.JeecgBootException;
import org.jeecg.common.util.DateUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.PropertyAccessorFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

/**
 * Spring Dao基类
 *
 * @author Bruce
 * @version 2016-3-31 14:45:07
 */
public abstract class BaseDao {
	/**
	 * Logger
	 */
	private static final Logger lg = LoggerFactory.getLogger("DataSource");
	private final static HashSet<String> cacheTableNames = new HashSet<>();

	private final static int batchSize = 500;
	private String dataBaseType = null;
	/**
	 * Jdbc Template
	 */
	@Autowired
	public JdbcTemplate jdbc;

	// =================================================================
	// O/R Mapping
	// =================================================================

	/**
	 * 解析O/R mapping的字段和值列表信息
	 *
	 * @param obj VO对象
	 * @param includeFields 插入的属性列表
	 * @param excludeFields 排除的属性列表
	 * @param mapping Class mapping信息
	 *
	 * @return List数组：0 - 数据库字段列表；1 - 值列表
	 */
	private List<?>[] getClassMappingFieldAndValueList(Object obj, String[] includeFields, String[] excludeFields, ClassMapping mapping) {
		List<?>[] result = new ArrayList[3];

		BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(obj);
		// Fields
		List<String> fields = new ArrayList<>(); // 要Insert的字段列表
		List<Object> fieldValues = new ArrayList<>(); // 字段值列表

		Map<String, DBMappingProperty> columns = mapping.getAllProperties();
		DBMappingProperty p;
		for (String colunmName : columns.keySet()) {
			p = columns.get(colunmName);

			// 主键字段特殊处理：有值 - 加入字段列表；无值或者为-1 - 忽略该字段
			if (mapping.getPkColumn().equals(colunmName.toUpperCase())) {
				// 忽略没有值的主键字段
				Object pkValue = bw.getPropertyValue(p.getName());
				if (null == pkValue || StringUtils.isBlank(pkValue.toString()) || "-1".equalsIgnoreCase(pkValue.toString())) {
					continue;
				}

				List<String> idList = new ArrayList<>();
				idList.add(pkValue.toString());
				result[2] = idList;
			}

			// 指定插入的字段
			if (null != includeFields && !ArrayUtils.contains(includeFields, p.getName())) {
				continue;
			}

			// 指定排除的字段
			if (null != excludeFields && ArrayUtils.contains(excludeFields, p.getName())) {
				continue;
			}

			Object value = bw.getPropertyValue(p.getName());
			if(mapping.isDynamic()) {
				if(mapping.getDynamicName().equalsIgnoreCase(p.getName())) {
					if(value == null || "".equals(value.toString())) {
						throw new JeecgBootException("动态表指定的列值不能为空。");
					}
					String sourceTable = mapping.getSourceName();
					mapping.setTableName(sourceTable + "_" + getDynamicOffset(obj));
				}
			}

			if(p.isDBField()){
				if((includeFields == null && excludeFields == null && value != null) || (includeFields != null && ArrayUtils.contains(includeFields, p.getName())) || (excludeFields != null)){
					// 加入该字段
					fields.add(colunmName);
					fieldValues.add(value);
				}
			}
		}

		result[0] = fields;
		result[1] = fieldValues;
		return result;
	}

	/**
	 * 保存对象至Table
	 *
	 * @param obj VO对象
	 *
	 * @return 数据库主键
	 */
	public <T> T insertIntoTable(final Object obj) {
		return insertIntoTable(obj, null, null);
	}

	/**
	 * 保存对象至Table
	 *
	 * @param obj VO对象
	 * @param includeFields 插入的属性列表
	 *
	 * @return 数据库主键
	 */
	public <T> T insertIntoTable(final Object obj, final String[] includeFields) {
		return insertIntoTable(obj, includeFields, null);
	}

	/**
	 * 删除数据
	 * @param id
	 */
	public int delete(final Object obj, long id) {
		ClassMapping mapping = BeanRowMapper.getClassMapping(obj.getClass());
		String sql = "delete from " + mapping.getTableName() + " where id = ? ";
		return this.jdbc.update(sql, id);
	}

	/**
	 * 删除数据
	 * @param id
	 */
	public int delete(final Object obj, String id) {
		ClassMapping mapping = BeanRowMapper.getClassMapping(obj.getClass());
		String sql = "delete from " + mapping.getTableName() + " where id = ? ";
		return this.jdbc.update(sql, id);
	}

	/**
	 * 插入对象至Table
	 *
	 * @param obj VO对象
	 * @param includeFields 插入的属性列表
	 * @param excludeFields 排除的属性列表
	 *
	 * @return 数据库主键
	 *
	 */
	@SuppressWarnings("unchecked")
	public <T> T insertIntoTable(final Object obj, final String[] includeFields, final String[] excludeFields) {
		List<?>[] fv1 = getInsertSQl(true, obj, includeFields, excludeFields);
		String sql = (String)fv1[0].get(0);
		lg.debug("Insert SQL：{}", sql);
		String pkColumn = (String)fv1[0].get(1);
		List<Object> fieldValues = (List<Object>) fv1[1];
		List<String> idValue = (List<String>) fv1[2];

		if(idValue == null){
			KeyHolder keyHolder = new GeneratedKeyHolder();
			jdbc.update((Connection connection) -> {
				PreparedStatement ps = connection.prepareStatement(sql, new String[] { pkColumn });
				// 参数列表
				Object param;
				for (int i = 0; i < fieldValues.size(); i++) {
					param = fieldValues.get(i);
					if (compareDBType(DataSourceType.ORACLE) && param instanceof Date) {
						ps.setObject(i + 1, new TIMESTAMP(new java.sql.Timestamp(((java.util.Date) param).getTime())));
					}else if (compareDBType(DataSourceType.DM) && param instanceof Date) {
						ps.setObject(i + 1, new Timestamp(((Date) param).getTime()));
					}else if (compareDBType(DataSourceType.POSTGRESQL) && param instanceof Date) {
						ps.setObject(i + 1, new Timestamp(((Date) param).getTime()));
					}else {
						lg.debug("Insert SQL param：{}", param);
						ps.setObject(i + 1, param);
					}
				}
				return ps;
			}, keyHolder);
			return (T)((Long)keyHolder.getKey().longValue());
		} else {
			String id = idValue.get(0);
			jdbc.update((Connection connection) -> {
				PreparedStatement ps = connection.prepareStatement(sql, new String[] { pkColumn });
				// 参数列表
				Object param;
				for (int i = 0; i < fieldValues.size(); i++) {
					param = fieldValues.get(i);
					if (compareDBType(DataSourceType.ORACLE) && param instanceof Date) {
						ps.setObject(i + 1, new TIMESTAMP(new java.sql.Timestamp(((java.util.Date) param).getTime())));
					}else if (compareDBType(DataSourceType.DM) && param instanceof Date) {
						ps.setObject(i + 1, new Timestamp(((Date) param).getTime()));
					}else if (compareDBType(DataSourceType.POSTGRESQL) && param instanceof Date) {
						ps.setObject(i + 1, new Timestamp(((Date) param).getTime()));
					}else {
						lg.debug("Insert SQL param：{}", param);
						ps.setObject(i + 1, param);
					}
				}
				return ps;
			});
			return (T)id;
		}
	}

	/**
	 * 更新对象属性至Table
	 *
	 * @param obj VO对象
	 * @param includeFields 更新的属性列表
	 * @param excludeFields 排除的属性列表
	 */
	@SuppressWarnings("unchecked")
	public void updateTable(Object obj, String[] includeFields, String[] excludeFields) {
		ClassMapping mapping = BeanRowMapper.getClassMapping(obj.getClass());
		String[] newArray = null;
		if(excludeFields != null && excludeFields.length > 0){
			newArray = new String[excludeFields.length + 1];
			// 复制原数组的元素到新数组
			for (int i = 0; i < excludeFields.length; i++) {
				newArray[i] = excludeFields[i];
			}
			newArray[newArray.length - 1] = "id";
		}
		List<?>[] fv = getClassMappingFieldAndValueList(obj, includeFields, newArray, mapping);
		// 要Update的字段列表
		List<String> fields = (List<String>) fv[0];
		String column;
		for (int i = 0; i < fields.size(); i++) {
			column = fields.get(i);
			fields.set(i, column + "=?");
		}

		// 检查数据，空值，长度

		// 字段值列表
		List<Object> fieldValues = (List<Object>) fv[1];
		// 主键字段值
		BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(obj);
		fieldValues.add(bw.getPropertyValue(mapping.getPkProperty()));

		// SQL
		String sql = "update " + mapping.getTableName() + " set " + StringUtils.join(fields, ", ") + " where " + mapping.getPkColumn() + "=?";
		lg.debug("Update SQL：{}", sql);

		jdbc.update(sql, fieldValues.toArray(new Object[0]));
	}

	/**
	 * 更新对象属性至Table
	 *
	 * @param obj VO对象
	 * @param includeFields 更新的属性列表
	 */
	public void updateTable(Object obj, String[] includeFields) {
		updateTable(obj, includeFields, null);
	}

	/**
	 * 编辑
	 *
	 * @param fieldName 字段集合
	 */
	public void updateInclude(Object obj, String... fieldName) {
		this.updateTable(obj, fieldName);
	}

	/**
	 * 编辑
	 *
	 * @param fieldName 字段集合
	 */
	public void updateExclude(Object obj, String... fieldName) {
		this.updateTable(obj, null, fieldName);
	}

	// =================================================================
	// 引用检测
	// =================================================================

	/**
	 * 检查字段是否存在重复值
	 *
	 * @param table 表名称
	 * @param fieldName 字段名
	 * @param value 字段值
	 * @param where 其它查询限制条件
	 *
	 * @return boolean
	 */
	public boolean isFieldDuplicate(String table, String fieldName, Object value, String where) {
		String sql = "select ID from " + table + " where " + fieldName + "= ? ";
		if (StringUtils.isNotBlank(where)){
			sql += " and " + where;
		}

		SqlRowSet rs = jdbc.queryForRowSet(sql, value);
		return rs.next();
	}

	/**
	 * 检查其它表是否有引用
	 *
	 * @param table 表名称
	 * @param fieldName 字段名
	 * @param value 字段值
	 * @param where 其它查询限制条件
	 * @return boolean
	 */
	public boolean isUsedByOtherTable(String table, String fieldName, Object value, String where) {
		String sql = "select count(*) as Num from " + table + " where " + fieldName + "=?" + (StringUtils.isNotBlank(where) ? " and " + where : "");
		SqlRowSet rs = jdbc.queryForRowSet(sql, value);
		rs.next();
		return (0 != rs.getInt("Num"));
	}

	// =================================================================
	// 查询
	// =================================================================

	/**
	 * 加载指定对象记录
	 *
	 * @param voClazz 映射Class类
	 * @param id 主键
	 */
	public <T> T load(Class<T> voClazz, Object id) {
		ClassMapping mapping = BeanRowMapper.getClassMapping(voClazz);
		List<T> oneRow = findByList("select * from " + mapping.getTableName() + " where " + mapping.getPkColumn() + "=?", voClazz, id);
		if (null != oneRow && 1 == oneRow.size()) {
			return oneRow.get(0);
		}else {
			return null;
		}
	}

	/**
	 * 列表查询
	 *
	 * @param sql SQL语句
	 * @param voClazz 映射Class类
	 * @param params SQL参数列表
	 */
	protected <T> List<T> findByList(String sql, Class<T> voClazz, Object... params) {
		return findByList(sql, params, voClazz);
	}

	/**
	 * 列表查询
	 *
	 * @param sql SQL语句
	 * @param voClazz 映射Class类
	 * @param pms SQL参数列表
	 */
	protected <T> List<T> findByList(String sql, Object[] pms, Class<T> voClazz) {
		Object[] params = handleParams(pms);
		return jdbc.query(sql, params, new BeanRowMapper<>(voClazz));
	}

	/**
	 * 前N条记录查询
	 *
	 * @param sql SQL语句
	 * @param voClazz 映射Class类
	 * @param pms SQL参数列表
	 */
	protected <T> List<T> findByTopN(String sql, Integer topN, Class<T> voClazz, Object... pms) {
		Object[] params = handleParams(pms);
		if (compareDBType(DataSourceType.MYSQL)) {
			sql = sql + " Limit 0," + topN; // start下标从0开始
		}else if (compareDBType(DataSourceType.ORACLE)|| compareDBType(DataSourceType.DM)
				|| compareDBType(DataSourceType.KINGBASE8) || (compareDBType(DataSourceType.OCEANBASE))) {
			sql = "select RowNum, forp.* from (" + sql + ") forp where RowNum<=" + topN;
		}else if (compareDBType(DataSourceType.POSTGRESQL)) {
			sql = sql + " Limit "+ topN +" offset 0" ;
		}
		lg.debug("Top N查询SQL：{}", sql);
		return jdbc.query(sql, params, new BeanRowMapper<>(voClazz));
	}

	/**
	 * 分页查询
	 *
	 * @param sql SQL语句
	 * @param voClazz O/R Mapping后的值对象Class
	 * @param ps 分页和排序信息
	 * @param params 查询条件
	 * @return page
	 */
	protected <T> PageInfo<T> findByPage(String sql, Class<T> voClazz, PageSort ps, Object... params) {
		return findByPage(sql, params, voClazz, ps);
	}

	/**
	 * 分页查询
	 *
	 * @param sql SQL语句
	 * @param pms 查询条件
	 * @param voClazz O/R Mapping后的值对象Class
	 * @param ps 分页和排序信息
	 * @return page
	 */
	protected <T> PageInfo<T> findByPage(String sql, Object[] pms, Class<T> voClazz, PageSort ps) {
		Object[] params = handleParams(pms);
		// TODO 优化 -1 == ps.getPageSize()查询所有记录方式：不查询记录总数。
		PageInfo<T> page = new PageInfo<>();

		// 统计记录总数
		String temp = "select count(*) " + sql.substring(sql.toLowerCase().indexOf("from"));
		if (compareDBType(DataSourceType.POSTGRESQL)){
			temp = "select count(*) over() " + sql.substring(sql.toLowerCase().indexOf("from"));
		}
		SqlRowSet rs = jdbc.queryForRowSet(temp, params);
		if (rs.next()){
			page.setTotal(rs.getLong(1));
		}

		// 如果开始记录位置 > 记录总数，则后退1页。
		if (((ps.getPageNo() - 1) * ps.getPageSize()) >= page.getTotal()){
			ps.setPageNo(ps.getPageNo() - 1);
		}

		// 检查倒退一页数据后，获取位置是否小于1
		if (ps.getPageNo() < 1){
			ps.setPageNo(1);
		}
		if (ps.getPageSize() < 1){
			ps.setPageNo(20);
		}
		lg.debug("分页参数：pageNumber-{}，pageSize-{}，sortName-{}，sortOrder-{}", ps.getPageNo(), ps.getPageSize(), ps.getColumn(), ps.getOrder());

		// 排序字段（如果有映射-转换为数据库字段名称）
		String sortField = "";
		if (StringUtils.isNotBlank(ps.getColumn())) {
			// 默认为VO属性名称
			sortField = ps.getColumn();

			Map<String, DBMappingProperty> columns = BeanRowMapper.getClassMapping(voClazz).getAllProperties();
			String dbColumName;
			DBMappingProperty pp;
			for (String s : columns.keySet()) {
				// Key - 数据库字段名称
				dbColumName = s;
				pp = columns.get(dbColumName);

				if (sortField.equals(pp.getName())) {
					sortField = dbColumName;
					lg.debug("数据库排序字段：{}->{}", ps.getColumn(), sortField);
					break;
				}
			}
		}

		// 排序 TODO 优化联合查询的结果集别名带来的排序字段的问题（外边统一包一层select * from (sql)语句）
		if (StringUtils.isNotBlank(sortField)) {
			sql += " order by " + sortField + " " + ps.getOrder();
		}

		// 分页
		if (compareDBType(DataSourceType.MYSQL)) {
			// 使用MySQL数据库函数进行分页
			sql = sql + " limit " + (ps.getPageNo() - 1) * ps.getPageSize() + "," + ps.getPageSize(); // start下标从0开始
		} else if (compareDBType(DataSourceType.ORACLE)|| compareDBType(DataSourceType.DM) || compareDBType(DataSourceType.KINGBASE8) || (compareDBType(DataSourceType.OCEANBASE))) {
			sql = "select f2.* from (select f1.*, RowNum frn from (" + sql + ") f1) f2 where f2.frn>" + (ps.getPageNo() - 1) * ps.getPageSize() + " and f2.frn<=" + ps.getPageNo() * ps.getPageSize();
		} else if (compareDBType(DataSourceType.POSTGRESQL)){
			sql = sql + " limit " + ps.getPageSize() + " offset " + (ps.getPageNo() - 1) * ps.getPageSize(); // start下标从0开始
		}

		lg.debug("分页查询SQL：{}", sql);
		List<T> rows = null;
		if(voClazz != null){
			rows = jdbc.query(sql, params, new BeanRowMapper<>(voClazz));
		} else {
			rows = (List<T>)jdbc.queryForList(sql, params);
		}
		page.setRecords(rows);
		page.setCurrent(ps.getPageNo());
		page.setSize(rows.size());
		page.setPages(page.getTotal() / ps.getPageSize());
		if(page.getTotal() % ps.getPageSize() != 0){
			page.setPages(page.getPages() + 1);
		}
		return page;
	}

	/**
	 * 记录告警日志内容
	 *
	 * @param category 日志类别：1 - 邮件；2 - 短信。
	 * @param level 级别：1 - 警告；2 - 错误。
	 * @param content 告警内容
	 */
	public void writeWarningLog(int category, int level, String content) {
		try {
			jdbc.update("insert into Forp_WarnLog (Category, LogLevel, Content, CreateDate) values(?, ?, ?, ?)", category, level, content.length() > 500 ? content.substring(0, 500) : content, new Date());
		} catch (Exception e) {
			lg.error("系统告警日志记录失败：", e);
		}
	}

	/**
	 * 根据主键获取名称
	 *
	 * @param table 数据库表名称
	 * @param colunmName 列名
	 * @param id 主键
	 *
	 * @return 字段值
	 */
	public String getValueById(String table, String colunmName, Object id) {
		String value = "未知";

		SqlRowSet rs = jdbc.queryForRowSet("select " + colunmName + " from " + table + " where ID=?", id);
		if (rs.next())
			value = rs.getString(colunmName);

		return value;
	}

	/**
	 * 根据SQL查询总数
	 * @param sql		SQL语句
	 * @param pms	参数
	 * @return
	 */
	public long getCount(String sql, Object... pms) {
		long count = 0;
		Object[] params = handleParams(pms);
		SqlRowSet rs = this.jdbc.queryForRowSet(sql, params);
		if(rs.next()) {
			count = rs.getLong(1);
		}
		return count;
	}

	/**
	 * 根据SQL更新或新增
	 * @param sql		SQL语句
	 * @param pms	参数
	 * @return
	 */
	protected int execute(String sql, final Object... pms) {
		Object[] params = handleParams(pms);
		int count = this.jdbc.update(sql, (p) -> {
			for(int i=0; i<params.length; i++) {
				p.setObject(i+1, params[i]);
			}
		});
		return count;
	}

	/**
	 * 检查数据，如果必填项，长度
	 * @param obj		对象
	 * @param fields	操作的列表
	 * @return
	 * @throws Exception
	 */
	private void checkedData(Object obj, List<String> fields) {
		String atText = null;
		Field field = null;
		ApiModelProperty apiModelAnn = null;
		DBColumn dbColumnAnn = null;
		PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(obj.getClass());
		BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(obj);
		for (PropertyDescriptor pd : pds) {
			if (null == pd.getWriteMethod() || null == pd.getReadMethod()) {
				continue;
			}

			field = BeanRowMapper.getObjectField(obj.getClass(), pd.getName());
			if(field == null) {
				throw new JeecgBootException("处理数据失败, field=" + pd.getName());
			}

			if (null == field.getAnnotations() || field.getAnnotations().length <= 0){
				continue;
			}

			atText = pd.getName();		// 默认为当前Field名称
			dbColumnAnn = field.getAnnotation(DBColumn.class);
			if (apiModelAnn != null && dbColumnAnn != null) {
				atText = dbColumnAnn.name().trim();
			}

			// 设置必填项
			apiModelAnn = field.getAnnotation(ApiModelProperty.class);
			if(apiModelAnn != null && checkedExist(atText, fields)) {
				if(apiModelAnn.required()) {
					Object value = bw.getPropertyValue(pd.getName());
					if(value instanceof String) {
						if(value != null && StringUtils.isBlank(value.toString())) {
							value = null;
						}
					}

					if(value == null) {
						throw new JeecgBootException(apiModelAnn.value() + "不能为空!");
					}
				}
			}
		}
	}

	private boolean checkedExist(String fieldName, List<String> fields) {
		for(String fd: fields) {
			if(fieldName.toUpperCase().equals(fd.toUpperCase())) {
				return true;
			}
		}

		return false;
	}

	/**
	 * 检查表名是否存在
	 * @param obj
	 */
	protected String checkedTableExist(Object obj) {
		Class<?> cls = obj.getClass();
		DBTable dbTable = cls.getAnnotation(DBTable.class);
		if (dbTable == null || !dbTable.isDynamic()) {
			return null;
		}

		String tableName = dbTable.name() + "_" + getDynamicOffset(obj);
		if(cacheTableNames.contains(tableName)) {
			// 缓存中存在
			return tableName;
		}

		try {
			this.jdbc.queryForList("select * from " + tableName + " where 1 = 2");
			cacheTableNames.add(tableName);
			return tableName;
		}  catch (Exception e) {
			return null;
		}
	}

	/**
	 * 检查表名是否存在，如果不存在复制表结构
	 * @param obj
	 * @throws SQLException
	 */
	protected String createDynamicTable(Object obj) {
		Class<?> cls = obj.getClass();
		DBTable dbTable = cls.getAnnotation(DBTable.class);
		String sourceName = dbTable.name();
		String targetName = sourceName + "_" + getDynamicOffset(obj);
		if(!compareDBType(DataSourceType.POSTGRESQL) && this.checkedTableExist(obj) != null) {
			return targetName;
		}

		try {
			synchronized (cacheTableNames) {
				if(!compareDBType(DataSourceType.POSTGRESQL) && this.checkedTableExist(obj) != null) {
					return targetName;
				}
				if (compareDBType(DataSourceType.MYSQL)) {
					// 复制表结构
					String sql = String.format("create table %s(id bigint(20) auto_increment primary key) as select * from %s where 1 = 2", targetName, sourceName);
					this.jdbc.execute(sql);
					cacheTableNames.add(targetName);
				} else if (compareDBType(DataSourceType.OCEANBASE)|| compareDBType(DataSourceType.ORACLE)|| compareDBType(DataSourceType.DM) || compareDBType(DataSourceType.KINGBASE8)) {
					// 复制表结构
					String sql = String.format("create table %s as select * from %s where 1 = 2", targetName, sourceName);
					this.jdbc.execute(sql);

					// 主键
					sql = String.format("alter table %s add constraint fk_%s_id primary key(id)", targetName, targetName);
					this.jdbc.execute(sql);

					// 创建序列
					sql = "create sequence SEQ_" + targetName;
					this.jdbc.execute(sql);

					// 创建序列
					sql = String.format("create or replace trigger T_%s_Insert before insert on %s for each row declare begin SELECT SEQ_%s.NextVAl INTO :new.id FROM dual;end %s_Insert;", targetName, targetName, targetName, targetName);
					this.jdbc.execute(sql);

					cacheTableNames.add(targetName);
				}else if (compareDBType(DataSourceType.POSTGRESQL)) {
					// 复制表结构
					String sql = String.format("CREATE TABLE if not exists %s (LIKE %s including constraints including indexes including comments including defaults)  DISTRIBUTE BY HASH(id);", targetName, sourceName);
					this.jdbc.execute(sql);

					cacheTableNames.add(targetName);
				}
			}
			return targetName;
		} catch (Exception e) {
			lg.error("执行SQL语句失败", e);
			try {
				String sql = String.format("drop table %s", targetName);
				this.jdbc.execute(sql);
			} catch (Exception ex) {ex.printStackTrace();}
			if (compareDBType(DataSourceType.ORACLE)){
				try {
					String sql = String.format("drop sequence SEQ_%s", targetName);
					this.jdbc.execute(sql);
				} catch (Exception ex) {ex.printStackTrace();}
			}
			throw new JeecgBootException("执行SQL语句失败!");
		}
	}

	public int getDynamicOffset(Object obj) {
		Class<?> cls = obj.getClass();
		DBTable dbTable = cls.getAnnotation(DBTable.class);
		if (dbTable == null || !dbTable.isDynamic()) {
			throw new JeecgBootException("内部错误，无法获取偏移量!");
		}

		BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(obj);
		Object objt = bw.getPropertyValue(dbTable.dynamicName());

		long value = 0;
		if(objt instanceof Long) {
			value = (Long)objt;
		} else if(objt instanceof Integer) {
			value = (Integer)objt;
		} else {
			throw new JeecgBootException("内部错误，无法获取偏移量值!");
		}
		int sign = (int) bw.getPropertyValue("ruleSign");
		if(sign == 1) {
			value = value % dbTable.dynamicOffset();
		}
		return (int) value;
	}

	/**
	 * 列表对象
	 * @param sql SQL语句
	 * @param voClazz 映射Class类
	 * @param params SQL参数列表
	 */
	public <T> T findOne(String sql, Class<T> voClazz, Object... params) throws JeecgBootException {
		List<T> list = findByList(sql, voClazz, params);
		if(list == null || list.size() == 0){
			return null;
		}else if(list.size() == 1){
			return list.get(0);
		}else{
			throw new JeecgBootException("无法获取唯一对象，可能存在多行数据！");
		}
	}

	/**
	 * 根据SQL查询单一值
	 * @param sql		SQL语句
	 * @param params	参数
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public <T> T findOneValue(String sql, Class<T> t, Object... params) {
		T t1 = null;
		SqlRowSet rs = this.jdbc.queryForRowSet(sql, handleParams(params));
		if(rs.next()) {
			if(t.getTypeName().equalsIgnoreCase(String.class.getTypeName())) {
				t1 = (T) rs.getString(1);
			} else if(t.getTypeName().equalsIgnoreCase(Long.class.getTypeName())) {
				t1 = (T) ((Long)rs.getLong(1));
			} else if(t.getTypeName().equalsIgnoreCase(Integer.class.getTypeName())) {
				t1 = (T) ((Integer)rs.getInt(1));
			} else {
				t1 = (T) rs.getObject(1);
			}
		}
		return t1;
	}

	/**
	 * 	处理参数
	 * @param params
	 * @return
	 */
	private Object[] handleParams(Object[] params) {
		if(params == null || params.length <= 0) {
			return new Object[] {};
		}

		if(params.length == 1 && params[0] instanceof List) {
			return (Object[]) params[0];
		}

		return params;
	}

	/**
	 * 批处理
	 * @param sql
	 * @param batchArgs
	 */
	public void batchExecute(String sql, List<Object[]> batchArgs) {
		List<Object[]> list = new ArrayList<>();
		int count = 0;
		for (Object[] batchArg : batchArgs) {
			list.add(batchArg);
			if (++count % batchSize == 0) {
				this.jdbc.batchUpdate(sql, list);
				list.clear();
			}
		}
		if(CollectionUtils.isNotEmpty(list)){
			this.jdbc.batchUpdate(sql, list);
		}
	}

	/**
	 * 批处理
	 */
	public void batchExecute(List<?> objList) {
		String sql = null;
		if(objList.isEmpty()){
			return;
		}
		Map<String, List<Object[]>> sqlDataMap = new HashMap<>();
		for(Object obj: objList){
			List<?>[] fv1 = getInsertSQl(true, obj, null, null);
			sql = (String)fv1[0].get(0);
			List<Object[]> list = sqlDataMap.get(sql);
			if(list == null){
				list = new ArrayList<>();
				sqlDataMap.put(sql, list);
			}
			lg.debug("Insert SQL：{}", sql);
			List<Object> fieldValues = (List<Object>) fv1[1];
			list.add(fieldValues.toArray());
			if (list.size() % batchSize == 0) {
				this.jdbc.batchUpdate(sql, list);
				list.clear();
			}
		}
		Iterator<String> iterator = sqlDataMap.keySet().iterator();
		while(iterator.hasNext()){
			sql = iterator.next();
			this.jdbc.batchUpdate(sql, sqlDataMap.get(sql));
		}
	}

	public String generateId() {
		return UUID.randomUUID().toString().replace("-", "");
	}

	/**
	 * @description : 数据库类型比较
	 * @Author: Geoff
	 * @date  : 2023/10/12 9:24
	 * @param : dbType
	 * @return : boolean
	 */
	protected boolean compareDBType(DataSourceType dbType){
		return dbType.getType().equalsIgnoreCase(getDataBaseType());
	}

	public List<?>[] getInsertSQl(Boolean isAdd, Object obj, String[] includeFields, String[] excludeFields){
		ClassMapping mapping = BeanRowMapper.getClassMapping(obj.getClass());
		String pkColumn = mapping.getPkColumn();
		if (compareDBType(DataSourceType.POSTGRESQL)){
			pkColumn = pkColumn.toLowerCase();
		}

		List<?>[] fv = getClassMappingFieldAndValueList(obj, includeFields, excludeFields, mapping);
		List<String> fields = (List<String>) fv[0];
		List<Object> fieldValues = (List<Object>) fv[1];
		List<Object> idsValues = (List<Object>) fv[2];

		if(isAdd && (idsValues == null || idsValues.size() <= 0) && mapping.getPkType().getTypeName().equalsIgnoreCase("java.lang.String")){
			String did = generateId();
			fields.add(pkColumn);
			fieldValues.add(generateId());
			List<String> idList = new ArrayList<>();
			idList.add(did);
			fv[2] = idList;
		}

		// SQL
		String sql = String.format("insert into %s (<fields>) values(<values>)", mapping.getTableName());
		if((compareDBType(DataSourceType.ORACLE)|| compareDBType(DataSourceType.DM) || compareDBType(DataSourceType.KINGBASE8)) && mapping.isDynamic()) {
			sql = String.format("insert into %s (%s, <fields>) values(%s, <values>)", mapping.getTableName(), "id", "seq_" + mapping.getTableName() + ".nextval");
		}
		sql = sql.replace("<fields>", StringUtils.join(fields, ", "));
		sql = sql.replace("<values>", StringUtils.repeat("?, ", fieldValues.size() - 1) + "?");

		if (compareDBType(DataSourceType.OCEANBASE)) {
			String[] tempSql = sql.split("values");
			String[] values = tempSql[1].split(",");
			String dateStr = "TO_DATE('%s','YYYY-MM-DD HH24:MI:SS')";
			Object fieldValue;
			String format;
			Iterator<Object> iterator = fieldValues.iterator();
			int index = 0;
			while (iterator.hasNext()){
				fieldValue = iterator.next();
				if (fieldValue instanceof Date){
					format = String.format(dateStr, DateUtil.formateDate((Date)fieldValue, "yyyy-MM-dd HH:mm:ss"));
					values[index] = values[index].replace("?",format);
					iterator.remove();
				}
				index++;
			}
			sql = tempSql[0]+" values "+StringUtils.join(values, ",");
		}
		fields.clear();
		fields.add(sql);
		fields.add(pkColumn);
		return fv;
	}

	public String getDataBaseType(){
		if(this.dataBaseType == null){
			try{
				String dbType = DBFUtil.getDbType(jdbc.getDataSource().getConnection().getMetaData().getDriverName());
				this.dataBaseType = dbType;
			} catch (Exception e){
				throw new JeecgBootException(e);
			}
		}
		return this.dataBaseType;
	}

	public void setJdbc(JdbcTemplate jdbc) {
		this.jdbc = jdbc;
	}

	public JdbcTemplate getJdbc() {
		return jdbc;
	}
}